"use server";

import db from "@/db/db";
import { testProducts, testOrders, testCustomers } from "@/db/schema/test-table-v2";
import { createTableService } from "@/components/client-table-v2/adapter/create-table-service";
import { DrizzleTableState } from "@/components/client-table-v2/adapter/drizzle-table-adapter";
import { productColumnDefs, OrderWithDetails, ServerColumnMeta } from "./column-defs";
import { SQL, count, eq, desc, sql, asc } from "drizzle-orm";
import { TestProduct } from "@/db/schema/test-table-v2";

// ============================================================
// Pattern 1: Client-Side - 전체 데이터 로드
// ============================================================

export async function getAllProducts() {
  return await db.select().from(testProducts).orderBy(testProducts.id);
}

// ============================================================
// Pattern 2: Factory Service - 자동 생성된 서버 액션
// ============================================================

// Server-side용 컬럼 정의 사용 (React 컴포넌트 없음)
export const getProductTableData = createTableService({
  db,
  schema: testProducts,
  columns: productColumnDefs,
});

// ============================================================
// Pattern 2-B: Factory Service with Grouping Support
// ============================================================

/**
 * 그룹 정보 타입
 */
export interface GroupInfo {
  groupKey: string;
  groupValue: string | number | boolean | null;
  count: number;
  // 확장 시 로드된 하위 행들
  rows?: TestProduct[];
}

/**
 * 그룹핑 응답 타입
 */
export interface GroupedResponse {
  groups: GroupInfo[];
  totalGroups: number;
}

/**
 * 일반 응답 타입
 */
export interface NormalResponse {
  data: TestProduct[];
  totalRows: number;
  pageCount: number;
}

/**
 * 서버 사이드 그룹핑을 지원하는 상품 테이블 데이터 조회
 * 
 * @param tableState - 테이블 상태 (pagination, sorting, filters, grouping)
 * @param expandedGroups - 확장된 그룹 키 목록 (예: ["category:Electronics", "status:active"])
 */
export async function getProductTableDataWithGrouping(
  tableState: DrizzleTableState,
  expandedGroups: string[] = []
): Promise<GroupedResponse | NormalResponse> {
  const { grouping, pagination } = tableState;
  
  // 그룹핑이 없으면 일반 조회
  if (!grouping || grouping.length === 0) {
    const result = await getProductTableData(tableState);
    return result as NormalResponse;
  }

  // 첫 번째 그룹핑 컬럼만 처리 (다중 그룹핑은 복잡도가 높음)
  const groupColumnId = grouping[0];
  
  // 서버 그룹핑 가능 여부 확인
  const columnDef = productColumnDefs.find(
    col => 'accessorKey' in col && col.accessorKey === groupColumnId
  );
  const meta = columnDef?.meta as ServerColumnMeta | undefined;
  
  if (!meta?.serverGroupable) {
    // 서버 그룹핑 불가 - 전체 데이터 반환하여 클라이언트에서 처리
    console.warn(`Column "${groupColumnId}" does not support server grouping. Falling back to client-side.`);
    const allData = await db.select().from(testProducts);
    return {
      data: allData,
      totalRows: allData.length,
      pageCount: 1,
    };
  }

  // 그룹별 카운트 조회
  const groupColumn = getProductColumn(groupColumnId);
  if (!groupColumn) {
    throw new Error(`Unknown column: ${groupColumnId}`);
  }

  const groupsResult = await db
    .select({
      groupValue: groupColumn,
      count: count(),
    })
    .from(testProducts)
    .groupBy(groupColumn)
    .orderBy(asc(groupColumn));

  // 그룹 정보 구성
  const groups: GroupInfo[] = await Promise.all(
    groupsResult.map(async (g) => {
      const groupKey = `${groupColumnId}:${g.groupValue}`;
      const isExpanded = expandedGroups.includes(groupKey);
      
      let rows: TestProduct[] | undefined;
      
      // 확장된 그룹의 하위 행 로드
      if (isExpanded) {
        rows = await db
          .select()
          .from(testProducts)
          .where(eq(groupColumn, g.groupValue))
          .orderBy(testProducts.id)
          .limit(pagination?.pageSize ?? 100); // 그룹 내 행 제한
      }

      return {
        groupKey,
        groupValue: g.groupValue,
        count: Number(g.count),
        rows,
      };
    })
  );

  return {
    groups,
    totalGroups: groups.length,
  };
}

/**
 * 컬럼 ID로 Drizzle 컬럼 객체 반환
 */
function getProductColumn(columnId: string) {
  const columnMap: Record<string, any> = {
    id: testProducts.id,
    sku: testProducts.sku,
    name: testProducts.name,
    category: testProducts.category,
    price: testProducts.price,
    stock: testProducts.stock,
    status: testProducts.status,
    isNew: testProducts.isNew,
    createdAt: testProducts.createdAt,
    updatedAt: testProducts.updatedAt,
  };
  return columnMap[columnId];
}

// ============================================================
// Pattern 3: Custom Service - 복잡한 조인 쿼리
// ============================================================

export async function getOrderTableData(tableState: DrizzleTableState): Promise<{
  data: OrderWithDetails[];
  totalRows: number;
  pageCount: number;
}> {
  // Pattern 3에서는 DrizzleTableAdapter를 사용하지 않습니다.
  // 조인된 결과의 컬럼들은 단일 테이블에 매핑되지 않기 때문입니다.
  // 대신, 페이지네이션 값만 직접 계산합니다.
  
  const pageSize = tableState.pagination?.pageSize ?? 10;
  const pageIndex = tableState.pagination?.pageIndex ?? 0;
  const limit = pageSize;
  const offset = pageIndex * pageSize;

  // Build ORDER BY clause based on sorting state
  const orderByClauses =
    tableState.sorting?.reduce<SQL<unknown>[]>((clauses, sort) => {
      const columnMap: Record<string, any> = {
        id: testOrders.id,
        orderNumber: testOrders.orderNumber,
        quantity: testOrders.quantity,
        unitPrice: testOrders.unitPrice,
        totalAmount: testOrders.totalAmount,
        status: testOrders.status,
        orderedAt: testOrders.orderedAt,
        customerName: testCustomers.name,
        customerEmail: testCustomers.email,
        customerTier: testCustomers.tier,
        productName: testProducts.name,
        productSku: testProducts.sku,
      };

      const column = columnMap[sort.id];
      if (!column) return clauses;

      clauses.push(sort.desc ? desc(column) : asc(column));
      return clauses;
    }, []) ?? [];

  // 커스텀 조인 쿼리 작성
  const data = await db
    .select({
      id: testOrders.id,
      orderNumber: testOrders.orderNumber,
      quantity: testOrders.quantity,
      unitPrice: testOrders.unitPrice,
      totalAmount: testOrders.totalAmount,
      status: testOrders.status,
      orderedAt: testOrders.orderedAt,
      // 고객 정보 조인
      customerName: testCustomers.name,
      customerEmail: testCustomers.email,
      customerTier: testCustomers.tier,
      // 상품 정보 조인
      productName: testProducts.name,
      productSku: testProducts.sku,
    })
    .from(testOrders)
    .leftJoin(testCustomers, eq(testOrders.customerId, testCustomers.id))
    .leftJoin(testProducts, eq(testOrders.productId, testProducts.id))
    .orderBy(...(orderByClauses.length > 0 ? orderByClauses : [desc(testOrders.orderedAt)]))
    .limit(limit)
    .offset(offset);

  // 총 개수 쿼리
  const totalResult = await db
    .select({ count: count() })
    .from(testOrders);

  const totalRows = Number(totalResult[0]?.count ?? 0);

  return {
    data: data as OrderWithDetails[],
    totalRows,
    pageCount: Math.ceil(totalRows / pageSize),
  };
}

// ============================================================
// Pattern 3-B: Custom Service with Grouping (Orders by Status)
// ============================================================

export interface OrderGroupInfo {
  groupKey: string;
  groupValue: string;
  count: number;
  totalAmount: number;
  rows?: OrderWithDetails[];
}

/**
 * 주문 데이터를 상태별로 그룹핑하여 조회
 */
export async function getOrderTableDataGroupedByStatus(
  expandedGroups: string[] = []
): Promise<{ groups: OrderGroupInfo[]; totalGroups: number }> {
  // 상태별 그룹 집계
  const groupsResult = await db
    .select({
      status: testOrders.status,
      count: count(),
      totalAmount: sql<number>`SUM(${testOrders.totalAmount}::numeric)`,
    })
    .from(testOrders)
    .groupBy(testOrders.status)
    .orderBy(testOrders.status);

  const groups: OrderGroupInfo[] = await Promise.all(
    groupsResult.map(async (g) => {
      const groupKey = `status:${g.status}`;
      const isExpanded = expandedGroups.includes(groupKey);
      
      let rows: OrderWithDetails[] | undefined;
      
      if (isExpanded) {
        // 확장된 그룹의 상세 주문 조회 (조인 포함)
        const orderRows = await db
          .select({
            id: testOrders.id,
            orderNumber: testOrders.orderNumber,
            quantity: testOrders.quantity,
            unitPrice: testOrders.unitPrice,
            totalAmount: testOrders.totalAmount,
            status: testOrders.status,
            orderedAt: testOrders.orderedAt,
            customerName: testCustomers.name,
            customerEmail: testCustomers.email,
            customerTier: testCustomers.tier,
            productName: testProducts.name,
            productSku: testProducts.sku,
          })
          .from(testOrders)
          .leftJoin(testCustomers, eq(testOrders.customerId, testCustomers.id))
          .leftJoin(testProducts, eq(testOrders.productId, testProducts.id))
          .where(eq(testOrders.status, g.status))
          .orderBy(desc(testOrders.orderedAt))
          .limit(50);
        
        rows = orderRows as OrderWithDetails[];
      }

      return {
        groupKey,
        groupValue: g.status,
        count: Number(g.count),
        totalAmount: Number(g.totalAmount) || 0,
        rows,
      };
    })
  );

  return {
    groups,
    totalGroups: groups.length,
  };
}